package com.wismay.gc.core.datasource.impl;

import java.io.File;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.wismay.gc.core.datasource.Datasource;
import com.wismay.gc.core.datasource.DbConnInfo;
import com.wismay.gc.core.metamode.Column;
import com.wismay.gc.core.metamode.MetaMode;
import com.wismay.gc.core.metamode.Table;
import com.wismay.gc.core.util.DriverPathUtil;

/**
 * @author Petter
 * 
 *         2009-9-24
 */
public class DatasourceForMysqlImpl implements Datasource {
	private static Logger log = LoggerFactory.getLogger(DatasourceForMysqlImpl.class);
	private DatabaseMetaData dbMetaData = null;
	private DbConnInfo dbConnInfo = null;

	private Connection conn = null;

	public static void main(String[] args) throws Exception {

		DbConnInfo dbConnInfo = new DbConnInfo("com.mysql.jdbc.Driver",
				"jdbc:mysql://localhost/gc?useUnicode=true&characterEncoding=utf-8", "root", "",
				"mysql-connector-java-5.1.6.jar");
		DatasourceForMysqlImpl dfm = new DatasourceForMysqlImpl(dbConnInfo);
		dfm.getMetaMode();
	}

	public DatasourceForMysqlImpl(DbConnInfo dbConnInfo) {
		super();
		this.dbConnInfo = dbConnInfo;
	}

	@Override
	public MetaMode getMetaMode() throws Exception {
		if (dbMetaData == null) {
			if (conn == null) {
				conn = getConn();
			}
			dbMetaData = conn.getMetaData();
		}

		MetaMode metaMode = new MetaMode(getTables());
		print(metaMode);
		return metaMode;
	}

	public Connection getConn() throws Exception {
		if (dbConnInfo == null) {
			throw new Exception("缺少数据库连接的必要信息(url、username、password etc.)。dbConnInfo is null");
		} else if (StringUtils.isBlank(dbConnInfo.getDriverFileName())) {
			throw new Exception("缺失数据库连接驱动。");
		}
		String driverPath = DriverPathUtil.DRIVER_PATH + File.separator + dbConnInfo.getDriverFileName();
		log.info("driverPath:" + driverPath);
		File driverJar = new File(driverPath);
		if (!driverJar.exists()) {
			throw new Exception("缺失数据库连接驱动文件==>" + driverPath);
		}

		String driverStr = dbConnInfo.getDriver();
		String url = dbConnInfo.getUrl();
		String user = dbConnInfo.getUsername();
		String password = dbConnInfo.getPassword();

		URLClassLoader loader = new URLClassLoader(new URL[] { driverJar.toURI().toURL() });
		@SuppressWarnings("unchecked")
		Class<Driver> clazz = (Class<Driver>) loader.loadClass(driverStr);
		Driver driver = clazz.newInstance();
		Properties p = new Properties();
		p.put("user", user);
		p.put("password", password);
		conn = driver.connect(url, p);
		return conn;
	}

	private void colseConn() throws SQLException {
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * 查询数据库中所有的表信息
	 * 
	 * @param tableName
	 * @return Map<String,Table>,key:表名字；value:{@link}Table
	 * @throws Exception
	 */
	private List<Table> getTables() throws Exception {
		List<Table> tables = new ArrayList<Table>();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SHOW TABLE STATUS");
		while (rs.next()) {
			String tableName = rs.getString("Name");
			String comment = rs.getString("Comment");
			Table t = new Table(tableName, comment);
			t.setColumns(getColumns(null, tableName));

			tables.add(t);
		}

		colseConn();// 关闭数据库连接
		return tables;
	}

	/**
	 * 获得表或视图中的所有列信息
	 * 
	 * @author Petter
	 */
	private List<Column> getColumns(String schemaName, String tableName) {
		List<Column> columns = new ArrayList<Column>();
		try {
			ResultSet rs = dbMetaData.getColumns(null, schemaName, tableName, "%");
			while (rs.next()) {
				String colName = rs.getString("COLUMN_NAME");
				int type = rs.getInt("DATA_TYPE");
				String typeName = rs.getString("TYPE_NAME");
				String comment = rs.getString("REMARKS");

				Column col = new Column(colName, typeName, type, comment);
				columns.add(col);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columns;
	}

	public void print(MetaMode metaMode) {

		for (Table t : metaMode.getTables()) {
			List<Column> cols = t.getColumns();

			System.out.println("-------------------");
			System.out.println("[" + t.getName() + "]" + t.getComment());
			for (Column c : cols) {
				System.out.println("    " + c.getColName() + " " + c.getTypeName() + " (" + c.getComment() + ")");

			}
		}
	}

	@Override
	public void testConn() throws Exception {
		if (conn == null) {
			conn = getConn();
		}
		colseConn();// 关闭数据库连接
		
	}

}